package com.community.service;

import com.alibaba.fastjson.JSONObject;
import com.community.entity.Major;
import com.community.entity.School;
import com.community.entity.Student;
import com.community.entity.StudentCollection;
import com.feng.fengDUtil.BaseService;
import com.community.util.ExcelReader;
import com.community.util.Search;
import com.community.util.SessionUtil;
import com.community.util.pdf.PdfUtil;
import com.feng.fengDUtil.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Pattern;


@Service
@Transactional
@Slf4j
public class MajorService extends BaseService {

    @Autowired
    private StudentService studentService;

    @Autowired
    private DictService dictService;

    public Map statics(Integer year,Integer batch){
        Search search = new Search();
        search.setBatch(batch);
        return statics(year,search);
    }

    private String queryCondition(Search search,String sql,List params){
        if(search.getYear()!=null){
            sql += " and u.year = ?";
            params.add(search.getYear());
        }
        String[] selectSubjects = search.getSelectSubjects();
        for(int i=0;i<selectSubjects.length;i++){
            String full = selectSubjects[i];
            if(i == 0){
                sql+= " and (u.subject = '不限'";
            }
            String subject=null;
            String[] shortSubs = {"地","物","技","生","史","化","政"};
            for(String sub:shortSubs){
                if(full.indexOf(sub)>-1){
                    subject = sub;
                }
            }
            if(StringUtils.isNotEmpty(subject)){
                sql += " or u.subject like ?";
                params.add(processLike(subject));
            }
            if(i==selectSubjects.length-1){
                sql+=")";
            }
        }
        if(search.getBatch()!=null){
            sql += " and u.batch = ?";
            params.add(search.getBatch());
        }
        if(search.getFromRank()!=null &&search.getToRank()!=null){
            sql += " and u.join_rank between ? and ?";
            params.add(search.getFromRank());
            params.add(search.getToRank());
        }
        if(search.getProvinces()!=null && search.getProvinces().length>0){
            List<String> provinces = Arrays.asList(search.getProvinces());
            if(provinces.indexOf("全国")==-1){
                sql +=" and u.sheng in "+genePlaceHolders(provinces.size());
                params.addAll(provinces);
            }
        }
        if(search.getMajors()!=null &&search.getMajors().length>0){
            String[] majors = search.getMajors();
            for(int i=0;i<majors.length;i++){
                if(i == 0){
                    sql+= " and u.level = ? and ( find_in_set(?,u.largeClass)";
                    params.add(search.getLevel());
                    params.add(majors[i]);
                }else{
                    sql += " or find_in_set(?,u.largeClass)";
                    params.add(majors[i]);
                }
                if(i==majors.length-1){
                    sql+=")";
                }
            }
        }
        if(StringUtils.isNotEmpty(search.getName())){
            String[] names = search.getName().split(" ");
            for(int i=0;i<names.length;i++){
                sql += " and concat(u.full_name,u.school_name) like ?";
                params.add( processLike(names[i]));
            }
        }
        if(search.getRank_inter()!=null){
            sql += " and u.id in  (select c.major_id from a_user_collection c where c.user_id = ?  and  c.rank_inter = ?)";
            params.add( SessionUtil.getCurStudentId());
            params.add(search.getRank_inter());
        }
        if(StringUtils.isNotEmpty(search.getOwnerId())){
            sql += " and u.id in  (select c.major_id from a_user_collection c where c.user_id = ?  )";
            params.add( search.getOwnerId());
        }
        return sql;
    }

    public Map statics(Integer year,Search search){

        List params = new ArrayList<>();
        String sql ="SELECT sum(u.pepole_num2) totalPeople,count(*) totalMajor,count(DISTINCT u.school_id) totalSchool FROM `a_major` u where 1=1";
        search.setYear(year);
        sql = queryCondition(search,sql,params);

        return sql(sql).one(Map.class,params.toArray());
    }

    public Page query(Page<Map> page, Search search) {
        ArrayList params = new ArrayList();

        String sql = "select u.id,u.school_code,u.code,u.full_name majorName,u.largeClass,u.subject,u.fee,u.join_score,u.join_rank, u.level majorLevel,u.schooling_length,u.pepole_num,u.pepole_num2,u.batch,u.year," +
                "ifnull(s.school_full_name,s.name) school_full_name, s.sheng,s.is985,s.is211,s.level schoolLevel,s.nature" +
                ",(select rank_inter From a_user_collection c where c.user_id = '"+search.getStudentId()+"' and c.major_id = u.id) checked" +
                " from a_major u join a_school s on u.school_id = s.id where 1=1 ";
        sql = queryCondition(search,sql,params);
        sql+=" order by u.join_rank asc";

        findPageList(Map.class,sql,page,params.toArray());

        if(StringUtils.isNotEmpty(search.getStudentId())){
            Student student = studentService.selectById(Student.class,search.getStudentId());
            page.getData().forEach(n->getChance(student.getRankNum(),n));
        }

        return page;
    }

    /**
     * @param rank
     * @param map
     */
    public void getChance(Integer rank,Map map){

        //取整数


        Integer join_rank = (Integer) map.get("join_rank");
        if(join_rank == null ||rank ==null){
            return;
        }

        Integer batch = MapUtils.getInteger(map,"batch");

        Integer[] threshold = dictService.getThreshold(batch);
        Integer min = threshold[0];
        Integer max = threshold[1];

        Integer chance = 50;
        //通过学生名次，专业录取最低名次计算录取概率
        if(rank<min){
            chance = 99;
        }else  if(rank>=min&&rank<=max){
            Integer base = 50+50*(rank-join_rank)/20000;
            if(base<=2){
                chance = 90;
            }
            else if(base>2&&base<98){
                chance = 100-base;
            }else {
                chance = 2;
            }
        }else{
            chance = 1;
        }
        map.put("chance",chance);
    }

    public List<Map> largeClass(Integer year, Search search) {
        List params = new ArrayList<>();
        sql("SET SESSION group_concat_max_len=102400").update(new Object[]{});
        String sql ="select level,group_concat(distinct largeClass) largeClass FROM `a_major` where year = ?";
        params.add(year);

        String[] selectSubjects = search.getSelectSubjects();

        for(int i=0;i<selectSubjects.length;i++){
            String full = selectSubjects[i];
            if(i == 0){
                sql+= " and (subject = '不限'";
            }
            String subject=null;
            String[] shortSubs = {"地","物","技","生","史","化","政"};
            for(String sub:shortSubs){
                if(full.indexOf(sub)>-1){
                    subject = sub;
                }
            }
            if(StringUtils.isNotEmpty(subject)){
                sql += " or subject like ?";
                params.add(processLike(subject));
            }
            if(i==selectSubjects.length-1){
                sql+=")";
            }
        }

        if(search.getBatch()!=null){
            sql += " and batch = ?";
            params.add(search.getBatch());
        }
        if(search.getProvinces()!=null && search.getProvinces().length>0){
            List<String> provinces = Arrays.asList(search.getProvinces());
            if(provinces.indexOf("全国")==-1){
                sql +=" and exists (select 1 from a_school s where s.id = school_id and s.sheng in "+genePlaceHolders(provinces.size())+")";
                params.addAll(provinces);
            }
        }
        if(search.getFromRank()!=null &&search.getToRank()!=null){
            sql += " and join_rank between ? and ?";
            params.add(search.getFromRank());
            params.add(search.getToRank());
        }
        sql +=" group by level";
        List<Map> maps = sql(sql).list(Map.class,params.toArray());
        maps.forEach(n->{
            String largeClass = MapUtils.getString(n,"largeClass");
            if(largeClass!=null){
                HashSet set =  new HashSet();
                largeClass = largeClass.replaceAll("，",",");
                set.addAll(Arrays.asList(largeClass.split(",")));
                n.put("largeClassList",set);
            }
        });
        return maps;
    }

    public void saveCollection(StudentCollection bean){
        Long exists = sql("select 1 from a_user_collection where major_id=? and user_id=? limit 1").one(Long.class,bean.getMajor_id(),bean.getUser_id());
        if(exists==null){
            saveOrUpdate(bean);
        }
    }

    public void delCollection(String majorId){
        sql("delete from a_user_collection where major_id = ? and user_id = ?").update(new Object[]{majorId, SessionUtil.getCurStudentId()});
    }

    public List<Map> listCategories(String type) {
       return sql("select name id,name text from a_major_type_type where type = ?").list(Map.class,type);
    }

    public List<Map> listSpecialties(String id) {
        return sql("select zyid id ,specialname text from a_specialty where zytype = ?").list(Map.class,id);
    }

    public JSONObject specialtyDetail(String id) {
        JSONObject ret =new JSONObject();
        ret.put("info",sql("select * from a_specialty where zyid = ?").one(Map.class,id));
        ret.put("list",sql("select * from a_specialty_school where zyid = ?").list(Map.class,id));
        return ret;
    }

    public List<Map> myCollections(String studentId){
        String sql = "select u.id,u.school_code,u.code,u.full_name majorName,u.largeClass,u.subject,u.fee,u.join_score,u.join_rank, u.level majorLevel,u.schooling_length,u.pepole_num,u.pepole_num2,u.batch," +
                "  s.school_full_name, s.sheng,s.is985,s.is211,s.level schoolLevel,s.nature,s.address" +
                "  from a_major u join a_school s on u.school_id = s.id  where u.id in  (select c.major_id from a_user_collection c where c.user_id = ?) order by u.join_rank asc";
        return sql(sql).list(Map.class,studentId);
    }

    public Workbook downloadCollections(String studentId) throws IOException {
        Workbook workbook = WorkbookFactory.create(true);

        Sheet sheet = workbook.createSheet("sheet1");
        Row topRow = sheet.createRow(0);
        topRow.createCell(0).setCellValue("志愿序号");
        topRow.createCell(1).setCellValue("院校名称");
        topRow.createCell(2).setCellValue("是否985");
        topRow.createCell(3).setCellValue("是否211");
        topRow.createCell(4).setCellValue("省");
        topRow.createCell(5).setCellValue("办学地址");
        topRow.createCell(6).setCellValue("性质");
        topRow.createCell(7).setCellValue("专业代码");
        topRow.createCell(8).setCellValue("专业全名");
        topRow.createCell(9).setCellValue("专业类别");
        topRow.createCell(10).setCellValue("科目范围");
        topRow.createCell(11).setCellValue("招生人数");
        topRow.createCell(12).setCellValue("往年计划");
        topRow.createCell(13).setCellValue("参考位次号");
        topRow.createCell(14).setCellValue("学制");
        topRow.createCell(15).setCellValue("学费参考");
        topRow.createCell(16).setCellValue("批次");

        List<Map> majors = myCollections(studentId);
        for(int i=0;i<majors.size();i++){
            Map item = majors.get(i);
            Row row = sheet.createRow(i+1);
            row.createCell(0).setCellValue(i+1);
            row.createCell(1).setCellValue(MapUtils.getString(item,"school_full_name") );

            row.createCell(2).setCellValue(MapUtils.getString(item,"is985"));
            row.createCell(3).setCellValue(MapUtils.getString(item,"is211"));
            row.createCell(4).setCellValue(MapUtils.getString(item,"sheng"));
            row.createCell(5).setCellValue(MapUtils.getString(item,"address"));
            row.createCell(6).setCellValue(MapUtils.getString(item,"nature"));
            row.createCell(7).setCellValue(MapUtils.getString(item,"code"));
            row.createCell(8).setCellValue(MapUtils.getString(item,"majorName"));
            row.createCell(9).setCellValue(MapUtils.getString(item,"largeClass"));
            row.createCell(10).setCellValue(MapUtils.getString(item,"subject"));
            row.createCell(11).setCellValue(MapUtils.getString(item,"pepole_num2"));
            row.createCell(12).setCellValue(MapUtils.getString(item,"pepole_num"));
            row.createCell(13).setCellValue(MapUtils.getInteger(item,"join_rank"));
            row.createCell(14).setCellValue(MapUtils.getString(item,"schooling_length"));
            row.createCell(15).setCellValue(MapUtils.getString(item,"fee"));
            row.createCell(16).setCellValue(MapUtils.getString(item,"batch"));

        }
        return workbook;
    }

    public Map uploadMajor(MultipartFile file,Search search) throws Exception{
        Integer inserted = 0;
        Integer ignored = 0;
        String curRow = "";
        InputStream ins =  file.getInputStream();
        Workbook wookbook = WorkbookFactory.create(ins);
        Date optime = new Date();
        Sheet sheet = wookbook.getSheetAt(0);
        Row topRow = sheet.getRow(0);
        Iterator<Cell> iterator = topRow.cellIterator();
        Integer i_code = null,i_name = null,i_school_code = null,i_school_full_name = null,i_address= null,
        i_largeClass = null,i_subject = null,i_pepole_num2 = null,i_pepole_num = null,
        i_schooling_length = null,i_fee = null,i_join_rank = null,i_level= null;

        while(iterator.hasNext()){
            Cell cell = iterator.next();
            String title = cell.getStringCellValue();
            switch (title){
                case "专业代码":i_code = cell.getColumnIndex();break;
                case "专业全名":i_name = cell.getColumnIndex();break;
                case "学校代码":i_school_code = cell.getColumnIndex();break;
                case "学校名称":i_school_full_name = cell.getColumnIndex();break;
                case "专业类别":i_largeClass = cell.getColumnIndex();break;
                case "科目范围":i_subject = cell.getColumnIndex();break;
                case "招生人数":i_pepole_num2 = cell.getColumnIndex();break;
                case "往年计划":i_pepole_num = cell.getColumnIndex();break;
                case "学制":i_schooling_length = cell.getColumnIndex();break;
                case "学费参考":i_fee = cell.getColumnIndex();break;
                case "录取参考":i_join_rank = cell.getColumnIndex();break;
                case "办学地址":i_address = cell.getColumnIndex();break;
                case "本科或专科":i_level = cell.getColumnIndex();break;
            }
        }
        if(i_code == null||i_name==null||i_school_code==null||i_school_full_name==null||i_largeClass==null||
           i_subject==null||i_pepole_num2==null||i_pepole_num==null||i_schooling_length==null||i_fee==null||
                i_join_rank==null||i_level == null){
            throw new Exception("专业excel与模板不一致,请确认!");
        }

        Map<String,String> schoolMap = new HashMap<>();
        for(int rowIndex =1,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if(row==null) continue;
            String school_name = ExcelReader.getString(row.getCell(i_school_full_name));
            String school_code = ExcelReader.getString(row.getCell(i_school_code));
            String address = ExcelReader.getString(row.getCell(i_address));
            if(StringUtils.isEmpty(school_name)){
                continue;
            }
            log.info(curRow);
            String schoolId = schoolMap.get(school_name);
            if(schoolId == null){
                schoolId = sql("select id from  a_school where name = ?").one(String.class,school_name);
                if(schoolId == null){
                    School school = new School();
                    school.setName(school_name);
                    school.setCode(school_code);
                    school.setAddress(address);
                    saveOrUpdate(school);
                    schoolId = school.getId();
                }
                schoolMap.put(school_name,schoolId);
            }

            String code = ExcelReader.getString(row.getCell(i_code));
            String name = ExcelReader.getString(row.getCell(i_name));

            //String school_full_name = ExcelReader.getString(row.getCell(i_school_full_name));
            String largeClass = ExcelReader.getString(row.getCell(i_largeClass));
            String subject = ExcelReader.getString(row.getCell(i_subject));
            Long pepole_num2 = ExcelReader.getLong(row.getCell(i_pepole_num2));
            String pepole_num = ExcelReader.getString(row.getCell(i_pepole_num));
            String schooling_length = ExcelReader.getString(row.getCell(i_schooling_length));
            String fee = ExcelReader.getString(row.getCell(i_fee));
            Long join_rank = ExcelReader.getLong(row.getCell(i_join_rank));
            String level = ExcelReader.getString(row.getCell(i_level));


            Major major = sql("select * from a_major where school_code = ? and code = ? and year = ? and batch = ?").one(Major.class,school_code,code, search.getYear(),search.getBatch());
            if(major == null){
                major = new Major();
                inserted++;
            }else{
                ignored++;
            }
            major.setBatch(search.getBatch());
            major.setYear(search.getYear()+"");
            major.setSchool_code(school_code);
            major.setSchool_name(school_name);
            major.setSchool_id(schoolId);
            major.setCode(code);
            major.setFull_name(name);
            if(StringUtils.isNotEmpty(largeClass)){
                major.setLargeclass(largeClass.replaceAll("，",","));
            }
            major.setSubject(subject);
            major.setPepole_num(pepole_num);
            major.setPepole_num2(pepole_num2!=null?pepole_num2.intValue():null);
            major.setSchooling_length(schooling_length);
            major.setFee(fee);
            major.setJoin_rank(join_rank!=null?join_rank.intValue():null);
            major.setLevel(level);
            saveOrUpdate(major);
        }
        sql(" update a_major a set a.sheng = (select sheng from a_school l where l.id = a.school_id limit 1 )").update(new Object[]{});

        HashMap data = new HashMap();
        data.put("inserted",inserted);
        data.put("ignored",ignored);
        return data;
    }

    public Map uploadMajorResult(MultipartFile file, Search search)throws Exception {
        Integer inserted = 0;
        Integer updated = 0;
        String curRow = "";
        InputStream ins =  file.getInputStream();
        Workbook wookbook = WorkbookFactory.create(ins);
        Date optime = new Date();
        Sheet sheet = wookbook.getSheetAt(0);
        Row topRow = sheet.getRow(0);
        Iterator<Cell> iterator = topRow.cellIterator();
        Integer i_code = null,i_name = null,i_school_code = null,i_school_full_name = null,i_pepole_num2 = null,
                i_score_total = null,i_join_rank = null;

        while(iterator.hasNext()){
            Cell cell = iterator.next();
            String title = cell.getStringCellValue();
            switch (title){
                case "专业代号":i_code = cell.getColumnIndex();break;
                case "专业名称":i_name = cell.getColumnIndex();break;
                case "学校代号":i_school_code = cell.getColumnIndex();break;
                case "学校名称":i_school_full_name = cell.getColumnIndex();break;
                case "计划数":i_pepole_num2 = cell.getColumnIndex();break;
                case "分数线":i_score_total = cell.getColumnIndex();break;
                case "位次":i_join_rank = cell.getColumnIndex();break;

            }
        }
        if(i_code == null||i_name==null||i_school_code==null||i_school_full_name==null||
               i_pepole_num2==null||i_score_total == null||
                i_join_rank==null){
            throw new Exception("专业excel与模板不一致,请确认!");
        }

        Map<String,String> schoolMap = new HashMap<>();
        for(int rowIndex =1,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if(row==null) continue;
            String school_name = ExcelReader.getString(row.getCell(i_school_full_name));
            String school_code = ExcelReader.getString(row.getCell(i_school_code));
            String code = ExcelReader.getString(row.getCell(i_code));
            String name = ExcelReader.getString(row.getCell(i_name));

            //String school_full_name = ExcelReader.getString(row.getCell(i_school_full_name));
            Long pepole_num2 = ExcelReader.getLong(row.getCell(i_pepole_num2));
            Long join_rank = ExcelReader.getLong(row.getCell(i_join_rank));
            Long score_total = ExcelReader.getLong(row.getCell(i_score_total));

            if(StringUtils.isEmpty(school_code)||score_total == null){
                continue;
            }
            log.info(curRow);
            Major major = sql("select * from a_major where school_code = ? and code = ? and year = ? and batch = ?").one(Major.class,school_code,code, search.getYear(),search.getBatch());
            if(major == null){
                major = new Major();
                String schoolId = schoolMap.get(school_code);
                if(schoolId == null){
                    schoolId = sql("select id from  a_school where code = ?").one(String.class,school_code);
                    if(schoolId == null){
                        School school = new School();
                        school.setName(school_name);
                        school.setCode(school_code);
                        school.setRanking(999999);
                        saveOrUpdate(school);
                        schoolId = school.getId();
                    }
                    schoolMap.put(school_code,schoolId);
                }
                major.setSchool_id(schoolId);
                major.setBatch(search.getBatch());
                major.setYear(search.getYear()+"");
                major.setSchool_code(school_code);
                major.setSchool_name(school_name);
                major.setCode(code);
                major.setFull_name(name);
                inserted++;
            }else{
                updated++;
            }
            major.setPepole_num2(pepole_num2!=null?pepole_num2.intValue():null);
            major.setJoin_rank(join_rank!=null?join_rank.intValue():null);
            major.setJoin_score(score_total.intValue());
            saveOrUpdate(major);
        }
        HashMap data = new HashMap();
        data.put("inserted",inserted);
        data.put("updated",updated);
        return data;
    }

    public Major uploadIntentions(MultipartFile file,Student student) throws Exception {
        List<String[]> list;
        if(file.getOriginalFilename().toLowerCase().endsWith("pdf")){
            list = uploadPdf(file);
        }else{
            list = uploadExcel(file);
        }
        for (String[] strs:list) {
            Major major = sql("select * from a_major where school_code = ? and code = ? and year = '2019' order by batch desc").one(Major.class,strs[0],strs[1]);
            if(major != null &&major.getJoin_score()!=null && student.getScore_total()!=null && student.getScore_total()>=major.getJoin_score()
                    &&major.getJoin_rank()!=null && student.getRankNum()!=null && student.getRankNum()>=major.getJoin_rank()){
                return major;
            }
        }
        return null;
    }

    public List<String[]> uploadExcel(MultipartFile file) throws Exception{
        List<String[]> list = new ArrayList<>();
        InputStream ins =  file.getInputStream();
        Workbook wookbook = WorkbookFactory.create(ins);
        Sheet sheet = wookbook.getSheetAt(0);
        Row topRow = sheet.getRow(0);
        Iterator<Cell> iterator = topRow.cellIterator();
        Integer i_code = null,i_school_code = null;
        while(iterator.hasNext()){
            Cell cell = iterator.next();
            String title = cell.getStringCellValue();
            switch (title){
                case "专业代码":i_code = cell.getColumnIndex();break;
                case "院校代码":i_school_code = cell.getColumnIndex();break;
            }
        }
        if(i_code == null||i_school_code==null){
            throw new Exception("专业excel与模板不一致,请确认!");
        }
        for(int rowIndex =1,totalRow = sheet.getLastRowNum();rowIndex<=totalRow;rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if(row==null) continue;
            String school_code = ExcelReader.getString(row.getCell(i_school_code));
            String code = ExcelReader.getString(row.getCell(i_code));

            if(StringUtils.isEmpty(school_code)||code == null){
                continue;
            }
            list.add(new String[]{school_code,code});
        }
        return list;
    }
    public List<String[]> uploadPdf(MultipartFile file){
        List<String[]> list = new ArrayList<>();
        String[] lines = PdfUtil.pdfToText(file);
        Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
        for(String line:lines){
            String[] words = line.split("[ \t]");
            if(words.length == 5 && pattern.matcher(words[0]).matches() == true){
                list.add(new String[]{words[1],words[3]});
            }
        }
       return list;
    }

}
